package com.ten_4_4.hiring.company.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;

import com.ten_4_4.hiring.comm.PageModel;
import com.ten_4_4.hiring.comm.util.DBUtil;
import com.ten_4_4.hiring.comm.util.Util;
import com.ten_4_4.hiring.company.po.ApplicationPo;
import com.ten_4_4.hiring.company.po.PositionPo;


/**
 * Application Dao
 *
 * @author Zzy.
 * @since 2016年10月14日
 */
public class ApplicationDao {

	private static final Logger LOG = Logger.getLogger(ApplicationDao.class);

	/**
	 * 分页查询某个求职者所有的求职信息
	 * 
	 * @param talentId
	 * @param pageNo
	 * @param pageSize
	 * @return
	 */
	public PageModel<ApplicationPo> listTalentApplyByPage(int talentId,
			int pageNo, int pageSize) {
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT ");
		sb.append("r.intention AS 'resume$intention', ");
		sb.append("p.name as 'position$name', ");
		sb.append("c.name as 'company$name', ");
		sb.append("a.id, ");
		sb.append("a.app_time, ");
		sb.append("a.`status`, ");
		sb.append("a.handle_time ");
		sb.append("FROM application a, resume r, position p, company c ");
		sb.append("WHERE a.resume_id = r.id AND a.position_id = p.id AND p.company_id = c.id ");
		sb.append("AND a.talent_id = ? ");
		sb.append("ORDER BY id DESC ");
		sb.append("LIMIT ?, ? ");

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sb.toString());
			preparedStatement.setInt(1, talentId);
			// 从哪条记录开始
			preparedStatement.setInt(2, (pageNo - 1) * pageSize);
			// 每页显示多少条记录
			preparedStatement.setInt(3, pageSize);
			resultSet = preparedStatement.executeQuery();
			// 每批数据
			List<ApplicationPo> applicationList = new ArrayList<>();
			while (resultSet.next()) {
				// 第一条数据
				ApplicationPo application = new ApplicationPo();
				application.setResume$intention(resultSet
						.getString("resume$intention"));
				application.setPosition$name(resultSet
						.getString("position$name"));
				application
						.setCompany$name(resultSet.getString("company$name"));
				application.setId(resultSet.getInt("id"));
				application.setAppTime(resultSet.getTimestamp("app_time"));
				application.setStatus(resultSet.getInt("status"));
				application
						.setHandleTime(resultSet.getTimestamp("handle_time"));
				// 把每一条数据放到一批数据里
				applicationList.add(application);
			}
			// 构建分页实体
			return new PageModel<ApplicationPo>(applicationList, pageNo,
					pageSize, getAllTalentApplyRecords(talentId));
		} catch (SQLException e) {
			Util.e(LOG, "分页查询某个求职者申请信息失败!", e);
			throw new RuntimeException();
		} finally {
			DBUtil.close(connection, preparedStatement, resultSet);
		}
	}

	/**
	 * 查询某个求职者申请信息的总记录数
	 * 
	 * @param talentId
	 * @return
	 */
	private static int getAllTalentApplyRecords(int talentId) {
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT ");
		sb.append("COUNT(*) ");
		sb.append("FROM application a, resume r, position p, company c ");
		sb.append("WHERE a.resume_id = r.id AND a.position_id = p.id AND p.company_id = c.id ");
		sb.append("AND a.talent_id = ? ");

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sb.toString());
			preparedStatement.setInt(1, talentId);
			resultSet = preparedStatement.executeQuery();
			resultSet.next();
			return resultSet.getInt(1);
		} catch (SQLException e) {
			Util.e(LOG, "分页时，查询某个求职者申请信息的总记录数失败!", e);
			throw new RuntimeException();
		} finally {
			DBUtil.close(connection, preparedStatement, resultSet);
		}
	}

	/**
	 * 分页查询某个公司所有的职位申请信息
	 * 
	 * @param applicationPo
	 * @param pageNo
	 * @param pageSize
	 * @return
	 */
	public PageModel<ApplicationPo> listCompanyVerifyByPage(
			ApplicationPo applicationPo, int pageNo, int pageSize) {
		List<Object> paramList = new ArrayList<>();
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT ");
		sb.append("p.name as 'position$name', ");
		sb.append("t.name as 'talent$name', ");
		sb.append("r.intention AS 'resume$intention', ");
		sb.append("a.id, ");
		sb.append("a.app_time, ");
		sb.append("a.`status`, ");
		sb.append("a.handle_time ");
		sb.append("FROM application a, resume r, position p, talent t ");
		sb.append("WHERE a.resume_id = r.id AND a.position_id = p.id AND a.talent_id = t.id ");
		sb.append("AND p.company_id = ? ");
		int companyId = applicationPo.getCompanyId();
		paramList.add(companyId);
		// 职位名称
		String positionName = applicationPo.getPosition$name();
		if (StringUtils.isNoneBlank(positionName)) {
			sb.append("AND p.name LIKE ? ");
			paramList.add("%" + applicationPo.getPosition$name() + "%");
		}
		// 审核状态
		int status = applicationPo.getStatus();
		if (status != 0) {
			sb.append("AND a.status = ? ");
			paramList.add(applicationPo.getStatus());
		}
		
		if (StringUtils.isNotBlank(applicationPo.getAppTimeStart())) {
			sb.append("AND a.app_time>=? ");
			paramList.add(applicationPo.getAppTimeStart());
		}
		if (StringUtils.isNotBlank(applicationPo.getAppTimeEnd())) {
			sb.append("AND a.app_time<=? ");
			paramList.add(applicationPo.getAppTimeEnd());
		}
		sb.append("ORDER BY a.id DESC LIMIT ?, ?");
		// sb.append("ORDER BY id DESC ");
		// sb.append("LIMIT ?, ? ");
		paramList.add((pageNo - 1) * pageSize);
		paramList.add(pageSize);

		return DBUtil.executeQueryByPage(ApplicationPo.class, pageNo,
				pageSize, sb.toString(), paramList);

		// Connection connection = null;
		// PreparedStatement preparedStatement = null;
		// ResultSet resultSet = null;
		// try {
		// connection = DBUtil.getConnection();
		// preparedStatement = connection.prepareStatement(sb.toString());
		// DBUtil.setParameter(paramList, preparedStatement);
		// resultSet = preparedStatement.executeQuery();
		// // 每批数据
		// List<ApplicationEntity> applicationList = new ArrayList<>();
		// while (resultSet.next()) {
		// // 第一条数据
		// ApplicationEntity application = new ApplicationEntity();
		// application.setPosition$name(resultSet
		// .getString("position$name"));
		// application.setTalent$name(resultSet.getString("talent$name"));
		// application.setResume$intention(resultSet
		// .getString("resume$intention"));
		// application.setId(resultSet.getInt("id"));
		// application.setAppTime(resultSet.getTimestamp("app_time"));
		// application.setStatus(resultSet.getInt("status"));
		// application
		// .setHandleTime(resultSet.getTimestamp("handle_time"));
		// // 把每一条数据放到一批数据里
		// applicationList.add(application);
		// }
		// // 构建分页实体
		// return new PageModel<ApplicationEntity>(applicationList, pageNo,
		// pageSize, getAllCompanyVerifyRecords(applicationEntity));
		// } catch (SQLException e) {
		// Util.e(LOG, "分页查询某个公司所有的职位申请信息失败!", e);
		// throw new RuntimeException();
		// } finally {
		// DBUtil.close(connection, preparedStatement, resultSet);
		// }
	}

	/**
	 * 查询某个公司所有的职位申请信息的总记录数
	 * 
	 * @param ApplicationPo
	 * @return
	 */
	// private static int getAllCompanyVerifyRecords(
	// ApplicationEntity applicationEntity) {
	// List<Object> paramList = new ArrayList<>();
	// StringBuilder sb = new StringBuilder();
	// sb.append("SELECT ");
	// sb.append("COUNT(*) ");
	// sb.append("FROM application a, resume r, position p, talent t ");
	// sb.append("WHERE a.resume_id = r.id AND a.position_id = p.id AND a.talent_id = t.id ");
	// sb.append("AND p.company_id = ? ");
	// int companyId = applicationEntity.getCompanyId();
	// paramList.add(companyId);
	// // 职位名称
	// String positionName = applicationEntity.getPosition$name();
	// if (StringUtils.isNoneBlank(positionName)) {
	// sb.append("AND p.name LIKE ? ");
	// paramList.add("%" + applicationEntity.getPosition$name() + "%");
	// }
	// // 审核状态
	// int status = applicationEntity.getStatus();
	// if (status != 0) {
	// sb.append("AND a.status = ? ");
	// paramList.add(applicationEntity.getStatus());
	// }
	//
	// Connection connection = null;
	// PreparedStatement preparedStatement = null;
	// ResultSet resultSet = null;
	// try {
	// connection = DBUtil.getConnection();
	// preparedStatement = connection.prepareStatement(sb.toString());
	// DBUtil.setParameter(paramList, preparedStatement);
	// resultSet = preparedStatement.executeQuery();
	// resultSet.next();
	// return resultSet.getInt(1);
	// } catch (SQLException e) {
	// Util.e(LOG, "分页时，查询某个公司所有的职位申请信息的总记录数失败!", e);
	// throw new RuntimeException();
	// } finally {
	// DBUtil.close(connection, preparedStatement, resultSet);
	// }
	// }

	/**
	 * 查询某个求职者所有的求职信息
	 * 
	 * @return
	 * @throws RuntimeException
	 * @Deprecated 此方法没有分页，请使用listByPage方法代替！
	 */
	@Deprecated
	public List<ApplicationPo> list(int talentId) {
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT ");
		sb.append("r.intention AS 'resume$intention', ");
		sb.append("p.name as 'position$name', ");
		sb.append("c.name as 'company$name', ");
		sb.append("a.id, ");
		sb.append("a.app_time, ");
		sb.append("a.`status`, ");
		sb.append("a.handle_time ");
		sb.append("FROM application a, resume r, position p, company c ");
		sb.append("WHERE a.resume_id = r.id AND a.position_id = p.id AND p.company_id = c.id ");
		sb.append("AND a.talent_id = ? ");
		sb.append("ORDER BY id DESC ");
		sb.append("LIMIT ?, ? ");
		List<ApplicationPo> applicationList = DBUtil.executeQuery(
				ApplicationPo.class, sb.toString(), talentId);
		return applicationList;
	}

	/**
	 * 删除某个求职者的求职信息
	 * 
	 * @param id
	 * @return
	 * @throws RuntimeException
	 */
	public int delete(int id) {
		String sql = "DELETE FROM application WHERE id=?";
		return DBUtil.executeUpdate(sql, id);
	}

	/**
	 * 求职者的简历投递
	 * 
	 * @param talentId
	 * @param resumeId
	 * @param positionId
	 * @return
	 * @throws RuntimeException
	 */
	public int add(int talentId, int resumeId, int positionId) {
		String sql = "INSERT INTO application VALUES(NULL, ?, ?, ?, 1, NOW(), NULL);";
		return DBUtil.executeUpdate(sql, positionId, talentId, resumeId);
	}

	/**
	 * 判断某个求职者是否已经投递某个职位
	 * 
	 * @param talentId
	 * @param id
	 * @return
	 */
	public ApplicationPo isApplied(int talentId, int id) {
		String sql = "SELECT * FROM application WHERE talent_id=? AND position_id=?";
		return DBUtil.getUniqueResult(ApplicationPo.class, sql, talentId,
				id);
	}

	/**
	 * 企业审核求职者的申请
	 * 
	 * @param status
	 * @param applicationId
	 * @return
	 */
	public int verify(int status, int applicationId) {
		String sql = "UPDATE application SET status=?, handle_time=NOW() WHERE id=?";
		return DBUtil.executeUpdate(sql, status, applicationId);
	}

	public PageModel<ApplicationPo> listTalentPositionPaging(int talentId, int pageNo, int pageSize) {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT ");
		sb.append("p.`name` AS 'position$name', ");
		sb.append("c.name AS company$name, ");
		sb.append("a.id, ");
		sb.append("a.app_time, ");
		sb.append("a.`status`, ");
		sb.append("a.handle_time ");
		sb.append("FROM application a, resume r, position p, company c, talent t ");
		sb.append("WHERE a.resume_id = r.id AND a.position_id = p.id AND p.company_id = c.id AND a.talent_id = t.id ");
		sb.append("AND t.id = ? ");
		sb.append("ORDER BY a.app_time ");
		sb.append("LIMIT ?, ? ");
		String sql = sb.toString();
		Connection conn = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<ApplicationPo> list = new ArrayList<>();
		PageModel<ApplicationPo> pageModel = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, talentId);
			ps.setInt(2, (pageNo - 1) * pageSize);
			ps.setInt(3, pageSize);
			rs = ps.executeQuery();
			while(rs.next()) {
				ApplicationPo application = new ApplicationPo();
				application.setPosition$name(rs.getString("position$name"));
				application.setCompany$name(rs.getString("company$name"));
				application.setId(rs.getInt("id"));
				application.setAppTime(rs.getTimestamp("app_time"));
				application.setStatus(rs.getInt("status"));
				application.setHandleTime(rs.getTimestamp("handle_time"));
				list.add(application);
			}
			pageModel = new PageModel<>(list, pageNo, pageSize, getTalentPositionCounts(talentId));
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return pageModel;
	}

	private int getTalentPositionCounts(int talentId) {
		String sql = "select count(id) from application where talent_id = ? ";
		return DBUtil.getCount(sql, talentId);
	}

	public int deleteApplication(int applicationId) {
		return DBUtil.executeUpdate("delete from application where id = ? ", applicationId);
	}

	/**
	 * false 表示没投递
	 */
	public boolean isDelivery(int talentId, int positionId) {
		String sql = "select id from application where talent_id = ? and position_id = ? ";
		List<ApplicationPo> result = DBUtil.executeQuery(ApplicationPo.class, sql, talentId, positionId);
		return result.size() == 0 ? false : true;
	}

	public int deliveryResume(int positionId, int talentId, int resumeId) {
		String sql = "INSERT INTO application VALUES(NULL, ?, ?, ?, 1, NOW(), NULL)";
		return DBUtil.executeUpdate(sql, positionId, talentId, resumeId);
	}
	

}
